const models = require('../db/mysqldb/index')
const { Op } = require('sequelize')

class SocketModel {
  /**
   * @description 获取用户socket_id信息
   * @param {string} user_id
   * @returns
   */
  static async getUserSocketId (user_id) {
    return await models.sys_user.findOne({
      where: {
        user_id,
        delete_timestamp: {
          [Op.is]: null,
        },
      },
    })
  }

  /**
   * @description socket_id获取用户user信息
   * @param {string} socket_id
   * @returns
   */
  static async getUserIdBySocketId (socket_id) {
    return await models.sys_user.findOne({
      where: {
        socket_id,
        delete_timestamp: {
          [Op.is]: null,
        },
      },
    })
  }

  /**
   * @description 用户添加socket_id
   * @param {string} user_id
   * @returns
   */
  static async addUserSocketId (user_id, socket_id) {
    return await models.sys_user.update(
      { socket_id, status: 1 },
      {
        where: {
          user_id,
          delete_timestamp: {
            [Op.is]: null,
          },
        },
      }
    )
  }

  /**
   * @description 用户删除socket_id
   * @param {string} user_id
   * @returns
   */
  static async removeUserSocketId (user_id) {
    return await models.sys_user.update(
      { socket_id: null, status: 0 },
      {
        where: {
          user_id,
          delete_timestamp: {
            [Op.is]: null,
          },
        },
      }
    )
  }


  /**
   * @description 获取所有未读条数
   * @param {string} user_id
   * @returns
   */
  static async getAllUnread (user_id) {
    let sql = `SELECT t1.num as new_friend_num,
                t2.num as group_msg_num,
                t3.num as friend_msg_num
            from (select count(1) num FROM chat_new_friends cnf WHERE cnf.to = ${user_id} and cnf.read = 0 and cnf.status = 0 and cnf.delete_timestamp is NULL) t1
            left join ( select count(1) num FROM chat_group_message cgm WHERE cgm.to = ${user_id} and cgm.read = 0 and cgm.delete_timestamp is NULL) t2 on 1 = 1
            left join ( select count(1) num FROM chat_private_message cpm WHERE cpm.to = ${user_id} and cpm.read = 0 and cpm.delete_timestamp is NULL) t3 on 1 = 1`
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    return result
  }

  /**
   * @description 获取在线人员 socket_id组
   * @param {string} user_id
   * @returns
   */
  static async findMembersId (members) {
    let sql = `select socket_id from sys_user where user_id in (${members}) and socket_id is null and delete_timestamp is null`
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    return result
  }

  /**
   * @description 获取在线好友 socket_id组
   * @param {string} user_id
   * @returns
   */
  static async findFriendsId (user_id) {
    let sql = `SELECT su.socket_id,cu.other_user_alias as user_alias FROM sys_user su left join chat_user_relation cu on su.user_id = cu.other_user_id WHERE su.user_id IN
                  ( SELECT other_user_id FROM chat_user_relation cur WHERE cur.user_id = ${user_id} and cur.delete_timestamp IS NULL) 
                  and su.delete_timestamp IS NULL and su.socket_id is NOT NULL GROUP BY su.user_id`
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    return result
  }
}
module.exports = {
  SocketModel,
}
